--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.
--Model Version 1.0.0.0

--This SQL is generated for the table 'Customer'


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_CustomerDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_CustomerDelete]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_CustomerDelete]
(
	@Original_CustomerId [UniqueIdentifier]
)
AS
SET NOCOUNT OFF;

DELETE FROM 
	[Customer] 
WHERE 
	[CustomerId] = @Original_CustomerId ;

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_CustomerSelectByCustomerPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_CustomerSelectByCustomerPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [dbo].[gen_CustomerSelectByCustomerPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
	[Customer].[customerid],
	[Customer].[name],
	[Customer].[CreatedBy],
	[Customer].[CreatedDate],
	[Customer].[ModifiedBy],
	[Customer].[ModifiedDate],
	[Customer].[TimeStamp]
FROM 
[Customer]
WHERE

	[Customer].[CustomerId] IN (SELECT [CustomerId] 
											FROM OpenXML(@hDoc, '//Item', 2) 
											WITH ([CustomerId] [Char] (36) 'CustomerId')) 


exec sp_xml_removeDocument @hDoc

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_CustomerSelectByCustomerSinglePk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_CustomerSelectByCustomerSinglePk]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_CustomerSelectByCustomerSinglePk]
(
	@CustomerId [UniqueIdentifier]
)
AS

SELECT 
	[Customer].[customerid],
	[Customer].[name],
	[Customer].[CreatedBy],
	[Customer].[CreatedDate],
	[Customer].[ModifiedBy],
	[Customer].[ModifiedDate],
	[Customer].[TimeStamp]

FROM
[Customer]
WHERE 
[Customer].[CustomerId] = @CustomerId 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_CustomerInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_CustomerInsert]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[gen_CustomerInsert]
(
	@CustomerId [UniqueIdentifier] = default,
	@Name [VarChar] (100) = default,
	@CreatedDate [DateTime],
	@CreatedBy [Varchar] (50),
	@ModifiedBy [Varchar] (50)

)
AS
SET NOCOUNT OFF;

if (@CreatedDate IS NULL)
SET @CreatedDate = getutcdate()
INSERT INTO [Customer]
(
	[CustomerId],
	[Name],
	[CreatedDate],
	[CreatedBy],
	[ModifiedDate],
	[ModifiedBy]
)
VALUES
(
	@CustomerId,
	@Name,
	@CreatedDate,
	@CreatedBy,
	@CreatedDate,
	@CreatedBy
);


SELECT 
	[Customer].[customerid],
	[Customer].[name],
	[Customer].[CreatedBy],
	[Customer].[CreatedDate],
	[Customer].[ModifiedBy],
	[Customer].[ModifiedDate],
	[Customer].[TimeStamp]

FROM
[Customer]
WHERE
	[Customer].[CustomerId] = @CustomerId;
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_CustomerPagingSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_CustomerPagingSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_CustomerPagingSelect]
(
	@page [Int], -- page number selected by the user
	@pageSize [Int], -- number of items on the page
	@orderByColumn [Varchar] (100), -- name of column to order things by
	@ascending [Bit], -- order column ascending or descending
	@filter [Varchar] (100) = null, -- filter statement passed in to determine like criteria on order by column
	@count [Int] out -- number of items in the collection
)
AS

SET NOCOUNT ON;

CREATE TABLE #tmpTable
(
	[CustomerId] [UniqueIdentifier]
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page)
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[customerid]
)
SELECT
	[Customer].[customerid]
FROM
[Customer]
WHERE
	(@orderByColumn = 'CustomerId' and (((@filter is null) or ([Customer].[CustomerId] is null)) or (@filter is not null and [Customer].[CustomerId] = @filter)))
or
	(@orderByColumn = 'Name' and (((@filter is null) or ([Customer].[Name] is null)) or (@filter is not null and [Customer].[Name] LIKE @filter)))
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'CustomerId' THEN [Customer].[CustomerId] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'CustomerId' THEN [Customer].[CustomerId] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Name' THEN [Customer].[Name] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Name' THEN [Customer].[Name] END END

-- set @count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @count = (
SELECT count(*)
FROM
[Customer]
WHERE
	(@orderByColumn = 'CustomerId' and (((@filter is null) or ([Customer].[CustomerId] is null)) or (@filter is not null and [Customer].[CustomerId] = @filter)))
or
	(@orderByColumn = 'Name' and (((@filter is null) or ([Customer].[Name] is null)) or (@filter is not null and [Customer].[Name] LIKE @filter)))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page) - @pageSize
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
SET ROWCOUNT @pageSize
SELECT
	[Customer].[customerid],
	[Customer].[name],
	[Customer].[CreatedBy],
	[Customer].[CreatedDate],
	[Customer].[ModifiedBy],
	[Customer].[ModifiedDate],
	[Customer].[TimeStamp]
FROM
	[#tmpTable]
	INNER JOIN [Customer] ON #tmpTable.[customerid] = [CUSTOMER].[customerid]
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'CustomerId' THEN [Customer].[CustomerId] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'CustomerId' THEN [Customer].[CustomerId] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Name' THEN [Customer].[Name] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Name' THEN [Customer].[Name] END END

DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_Customer__AUDIT_SELECT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_Customer__AUDIT_SELECT]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_Customer__AUDIT_SELECT]
(
 @CustomerId [UniqueIdentifier],
 @__auditType [int] = default,
 @__pageOffset [int] = 1,
 @__recordsPerPage [int] = default,
 @__startDate [datetime] = default,
 @__endDate [datetime] = default
)
AS

SET NOCOUNT ON;

if(@__pageOffset < 1 ) SET @__pageOffset = 1
if((NOT (@__recordsPerPage IS NULL)) AND (@__recordsPerPage < 1 )) SET @__recordsPerPage = 1
IF (@__recordsPerPage IS NULL)
BEGIN
SELECT
	[__AUDIT__Customer].[__action],
	[__AUDIT__Customer].[__insertdate],
	[__AUDIT__Customer].[ModifiedBy],
	[__AUDIT__Customer].[CustomerId],
	[__AUDIT__Customer].[Name]
FROM
	[__AUDIT__Customer]
WHERE
	((@__auditType IS NULL) OR ([__AUDIT__Customer].__action = @__auditType)) AND
	((@__startDate IS NULL) OR ([__AUDIT__Customer].__insertdate >= @__startDate)) AND
	((@__endDate IS NULL) OR ([__AUDIT__Customer].__insertdate < @__endDate)) AND
	(@CustomerId = [__AUDIT__Customer].CustomerId)
	ORDER BY [__AUDIT__Customer].__insertdate DESC

END
ELSE
BEGIN
SELECT
	[__TempTable].[__action],
	[__TempTable].[__insertdate],
	[__TempTable].[ModifiedBy],
	[__TempTable].[CustomerId],
	[__TempTable].[Name]
FROM
	(SELECT  ROW_NUMBER() OVER (ORDER BY [__AUDIT__Customer].__insertdate DESC)
	AS __row,
	[__AUDIT__Customer].[__action],
	[__AUDIT__Customer].[__insertdate],
	[__AUDIT__Customer].ModifiedBy,
	[__AUDIT__Customer].[CustomerId],
	[__AUDIT__Customer].[Name]
	FROM 
		[__AUDIT__Customer]
	WHERE
		((@__auditType IS NULL) OR ([__AUDIT__Customer].__action = @__auditType)) AND
	((@__startDate IS NULL) OR ([__AUDIT__Customer].__insertdate >= @__startDate)) AND
	((@__endDate IS NULL) OR ([__AUDIT__Customer].__insertdate < @__endDate)) AND
		(@CustomerId = [__AUDIT__Customer].CustomerId)
	)
	AS [__TempTable]
WHERE
	__row >= (((@__pageOffset-1)*@__recordsPerPage)+1) AND __row <= (@__pageOffset*@__recordsPerPage)

END
return (select COUNT(*) 
	FROM [__AUDIT__Customer] WHERE
		((@__auditType IS NULL) OR ([__AUDIT__Customer].__action = @__auditType)) AND
	((@__startDate IS NULL) OR ([__AUDIT__Customer].__insertdate >= @__startDate)) AND
	((@__endDate IS NULL) OR ([__AUDIT__Customer].__insertdate < @__endDate)) AND
		(@CustomerId = [__AUDIT__Customer].CustomerId)
		)

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_CustomerUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_CustomerUpdate]
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_CustomerUpdate]
(
	@Name [VarChar] (100),
	@ModifiedBy [Varchar] (50),
	@ModifiedDate [DateTime] = null,
	@Original_CustomerId [UniqueIdentifier],
	@Original_TimeStamp timestamp
)
AS

IF (@ModifiedDate IS NULL)
SET @ModifiedDate = getutcdate();

SET NOCOUNT OFF;
UPDATE 
[Customer] 
SET
	[Name] = @Name,
	[ModifiedBy] = @ModifiedBy,
	[ModifiedDate] = @ModifiedDate

WHERE
	[Customer].[CustomerId] = @Original_CustomerId AND
	[Customer].[TimeStamp] = @Original_TimeStamp


SELECT
	[Customer].[customerid],
	[Customer].[name],
	[Customer].[CreatedBy],
	[Customer].[CreatedDate],
	[Customer].[ModifiedBy],
	[Customer].[ModifiedDate],
	[Customer].[TimeStamp]
FROM 
[Customer]
WHERE
	[Customer].[CustomerId] = @Original_CustomerId
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_CustomerSelectByCreatedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_CustomerSelectByCreatedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_CustomerSelectByCreatedDateRange]
(
	@start_date [DateTime],
	@end_date [DateTime]
)
AS

SET NOCOUNT ON;

SELECT
	[Customer].[customerid],
	[Customer].[name],
	[Customer].[CreatedBy],
	[Customer].[CreatedDate],
	[Customer].[ModifiedBy],
	[Customer].[ModifiedDate],
	[Customer].[TimeStamp]
FROM
[Customer]
WHERE
((([Customer].[CreatedDate] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [Customer].[CreatedDate])) AND 
((([Customer].[CreatedDate] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [Customer].[CreatedDate]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_CustomerSelectByModifiedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_CustomerSelectByModifiedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_CustomerSelectByModifiedDateRange]
(
	@start_date [DateTime],
	@end_date [DateTime]
)
AS

SET NOCOUNT ON;

SELECT
	[Customer].[customerid],
	[Customer].[name],
	[Customer].[CreatedBy],
	[Customer].[CreatedDate],
	[Customer].[ModifiedBy],
	[Customer].[ModifiedDate],
	[Customer].[TimeStamp]
FROM
[Customer]
WHERE
((([Customer].[ModifiedDate] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [Customer].[ModifiedDate])) AND 
((([Customer].[ModifiedDate] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [Customer].[ModifiedDate]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_CustomerSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_CustomerSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_CustomerSelect]
AS

SET NOCOUNT ON;

SELECT
	[Customer].[customerid],
	[Customer].[name],
	[Customer].[CreatedBy],
	[Customer].[CreatedDate],
	[Customer].[ModifiedBy],
	[Customer].[ModifiedDate],
	[Customer].[TimeStamp]
FROM 
[Customer]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

